﻿using LJLib.Text.Parser;
using LongjoePublic;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;
using System.Windows.Forms;
using System.Xml;

namespace RN_DirectService_init
{
    public partial class FrmCUST : Form
    {
        // 账套集合
        private List<CompanyInfo> infos = null;

        // 解密key
        private readonly string SystemKey = "123457851239866";

        private string refDBConnStr = string.Empty;

        private string refTableName = string.Empty;

        // 账套信息(主、邮件数据库连接字符串)类
        private class CompanyInfo
        {
            // 账套名
            public string accountName { get; set; }

            // 主数据库连接字符串
            public string mainDB { get; set; }

            // 主数据库名称
            public string mainDBName { get; set; }

            // 邮件数据库连接
            public string emailDB { get; set; }
        }

        // 数据表LJFIEB_apicmd对应封装类
        private class LJFIEB_apicmd
        {
            public Guid serialNum { get; set; }
            public string apiname { get; set; }
            public string apibody { get; set; }
        }

        public FrmCUST()
        {
            InitializeComponent();
        }

        // 【按钮】读取配置文件
        private void btnFile_Click(object sender, EventArgs e)
        {
            infos = new List<CompanyInfo>();

            try
            {
                Trace.Write("----------加载配置文件内的账套信息----------");
                LoadXML();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                Trace.Write(ex.ToString());
                return;
            }

            if (infos.Count > 0)
            {
                MessageBox.Show("加载账套信息成功");
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < infos.Count; i++)
                {
                    sb.AppendLine(string.Format("{0}:{1}", i + 1, infos[i].accountName));
                    sb.AppendLine(infos[i].mainDB + "----[" + infos[i].mainDBName + "]");
                    sb.AppendLine(infos[i].emailDB);
                    sb.AppendLine();
                }
                txtMsg.Text = sb.ToString();
            }
            else
            {
                MessageBox.Show("没有获取任何账套信息！！！");
            }
        }

        // 从配置文件(xml)读取账套信息
        private void LoadXML()
        {
            XmlDocument xmldoc = new XmlDocument();
            xmldoc.Load(txtFilePath.Text);
            XmlElement xmlRoot = xmldoc.DocumentElement;
            if (xmlRoot.HasChildNodes)
            {
                for (int i = 0; i < xmlRoot.ChildNodes.Count; i++)
                {
                    if (xmlRoot.ChildNodes[i].Name.Contains("UT"))
                    {
                        CompanyInfo info = new CompanyInfo();
                        XmlNode node = xmlRoot.ChildNodes[i];

                        info.accountName = xmlRoot.ChildNodes[i].InnerText;

                        string dbname = psw_bczh3.GetEntrypt(node.Attributes["DataBase"].Value, 1);
                        string dbuser = psw_bczh3.GetEntrypt(node.Attributes["Uid"].Value, 1);
                        string dbpwd = psw_bczh3.GetEntrypt(psw_bczh3.GetEntrypt(node.Attributes["Pwd"].Value, 1), 1, SystemKey);
                        //string dbname = (node.Attributes["DataBase"].Value);
                        //string dbuser = (node.Attributes["Uid"].Value);
                        //string dbpwd = (node.Attributes["Pwd"].Value);
                        info.mainDB = GetDBConnStr(dbname, dbuser, dbpwd);
                        info.mainDBName = GetDBName(info.mainDB);

                        string emname = psw_bczh3.GetEntrypt(node.Attributes["DataBaseMsg"].Value, 1);
                        string emuser = psw_bczh3.GetEntrypt(node.Attributes["UidMsg"].Value, 1);
                        string empwd = psw_bczh3.GetEntrypt(psw_bczh3.GetEntrypt(node.Attributes["PwdMsg"].Value, 1), 1, SystemKey);
                        //string emname = (node.Attributes["DataBaseMsg"].Value);
                        //string emuser = (node.Attributes["UidMsg"].Value);
                        //string empwd = (node.Attributes["PwdMsg"].Value);
                        info.emailDB = GetDBConnStr(emname, emuser, empwd);
                        
                        infos.Add(info);
                    }
                }
            }
        }

        // 拼接数据库连接字符串(本地数据库)
        private string GetDBConnStr(string dbname, string user, string pwd)
        {
            return "Data Source=127.0.0.1;Initial Catalog=" + dbname + ";User ID=" + user + ";Password=" + pwd + ";";
        }

        // 获取连接字符串中的数据库名称
        private string GetDBName(string connStr)
        {
            if (!connStr.Contains("Initial Catalog=") || !connStr.Contains(";User ID="))
            {
                return "";
            }

            int index = connStr.IndexOf("Initial Catalog=") + 16;
            int len = connStr.IndexOf(";User ID=") - index;

            return connStr.Substring(index, len).Trim();
        }

        // 【按钮】执行(身份绑定)
        private void btnGo_Click(object sender, EventArgs e)
        {
            if (infos == null || infos.Count == 0)
            {
                MessageBox.Show("没有账套信息");
                return;
            }

            if (MessageBox.Show("确定要执行？", "", MessageBoxButtons.YesNoCancel) != DialogResult.Yes)
            {
                MessageBox.Show("操作取消");
                return;
            }

            Trace.Write("----------身份绑定操作开始----------");

            foreach (var info in infos)
            {
                try
                {
                    ResetCusComm(info);
                    UpdateSPT(info);
                    PushPacket(info);
                }
                catch (Exception ex)
                {
                    txtMsg.Text += string.Format("失败！！:账套[{0}]执行失败:{1}{2}{2}", info.accountName, ex.Message, System.Environment.NewLine);
                    Trace.Write(ex.ToString());
                    continue;
                }
                txtMsg.Text += string.Format("PASS:账套[{0}]执行成功{1}{1}", info.accountName, System.Environment.NewLine);
            }

            txtMsg.Text += "执行完成(身份绑定)\n\n";
            MessageBox.Show("身份绑定完成！请检查日志");
        }

        // 重置LJFIEB_CusComm_RelMx、LJFIEB_CusComm
        private void ResetCusComm(CompanyInfo info)
        {
            using (var conn = new SqlConnection(info.emailDB))
            using (var cmd = conn.CreateCommand())
            {
                conn.Open();
                
                // 清空LJFIEB_CusComm_RelMx
                cmd.CommandText = "delete LJFIEB_CusComm_RelMx";
                cmd.ExecuteNonQuery();

                // 清除LJFIEB_CusComm外企(工厂)信息
                cmd.CommandText = "delete LJFIEB_CusComm where CusType <> 0";
                cmd.ExecuteNonQuery();

                // 将工厂(总部)的真实信息
                cmd.CommandText = @"
insert into LJFIEB_CusComm 
(CusCommID, 
CusType, 
CusCode, 
CusName, 
CusKey, 

IP, 
Port1, 
sys_changetime, 
Accept, 
CusID, 

lastIP, 
lastPort, 
lastIP_time, 
IfDynamic) 

select 
MAX(CusCommID) + 1, 
1, 
'RUNNIAN', 
'RUNNIAN', 
'', 

'118.112.183.49', 
'20000', 
GETDATE(), 
1, 
'05D13572-488D-408A-A20C-D89FB467E94A', 

'118.112.183.49', 
'20000', 
GETDATE(), 
0 
from LJFIEB_CusComm
";
                cmd.ExecuteNonQuery();
            }
        }

        // 更新供应商信息u_spt
        private void UpdateSPT(CompanyInfo info)
        {
            using (var conn = new SqlConnection(info.mainDB))
            using (var cmd = conn.CreateCommand())
            {
                conn.Open();

                // 重置使用电子商务的供应商旧数据
                cmd.CommandText = "update u_spt    set ifecomm = 0, commcode = '', commsccode = '', commMyRelID = 0    where ifecomm  = 1 or commcode <> '' or commsccode <> ''";
                cmd.ExecuteNonQuery();

                // 更新供应商电子商务信息
                cmd.CommandText = @"
update u_spt 
set ifecomm = 1, commcode = 'RUNNIAN', commsccode = 
    case when name = '吉森' then '02'
         when name = '锐奇' then '03'
         when name = '优米诺' then '05'
         when name = '艾驰(新)' then '05'
         when name = '槟城印象' then '03'
         when name = '艾驰' then '02'
         when name = '沙发' then '06'
    end 
where name in ('吉森', '锐奇', '优米诺', '艾驰(新) ', '槟城印象', '艾驰', '沙发')
";
                cmd.ExecuteNonQuery();
            }
        }

        // 推送、add_packet重写
        private void PushPacket(CompanyInfo info)
        {
            LJFIEB_apicmd apicmd = null;

            // 从主数据库获取信息
            using (var connMain = new SqlConnection(info.mainDB))
            using (var cmdMain = connMain.CreateCommand())
            {
                connMain.Open();

                cmdMain.CommandText = "select sptid, sptcode, name, commMyRelID    from u_spt    where commcode = 'RUNNIAN' and ifecomm = 1";
                using (var readerMain = cmdMain.ExecuteReader())
                {
                    List<RelidItem> list = null;
                    Dictionary<string, List<RelidItem>> dic = null;
                    if (readerMain.HasRows)
                    {
                        list = new List<RelidItem>();
                        while (readerMain.Read())
                        {
                            int relid = Convert.ToInt32(readerMain["sptid"]);
                            int reltype = 1; // 供应商
                            string relcode = readerMain["sptcode"].ToString().Trim();
                            string relname = readerMain["name"].ToString().Trim();
                            int bindreltype = 2; // 客户
                            int bindrelid = Convert.ToInt32(readerMain["commMyRelID"]);
                            list.Add(new RelidItem(relid, reltype, relcode, relname, bindreltype, bindrelid));
                        }
                    }

                    if (list != null && list.Count > 0)
                    {
                        dic = new Dictionary<string, List<RelidItem>>();
                        dic.Add("relids", list);

                        apicmd = new LJFIEB_apicmd();
                        apicmd.serialNum = Guid.NewGuid();
                        apicmd.apiname = "SendIDs";
                        apicmd.apibody = new LJParser().ToJson(dic);
                    }
                }
            }

            // 没有内容则返回
            if (apicmd == null)
            {
                return;
            }

            // 将信息插入邮件数据库，待邮件后台推送
            using (var connEmail = new SqlConnection(info.emailDB))
            using (var cmdEmail = connEmail.CreateCommand())
            {
                connEmail.Open();

                cmdEmail.CommandText = "INSERT LJFIEB_apicmd(SerialNum, apiname, apibody)    VALUES (@SerialNum, @apiname, @apibody)";
                cmdEmail.Parameters.AddWithValue("@SerialNum", apicmd.serialNum);
                cmdEmail.Parameters.AddWithValue("@apiname", apicmd.apiname);
                cmdEmail.Parameters.AddWithValue("@apibody", apicmd.apibody);
                cmdEmail.ExecuteNonQuery();

                int sendCusCommID, receiveCusCommID;
                string sendCusID, receiveCusID;

                cmdEmail.CommandText = "SELECT CusCommID, CusID    FROM LJFIEB_CusComm    WHERE CusType = 0";
                cmdEmail.Parameters.Clear();
                using (var reader = cmdEmail.ExecuteReader())
                {
                    reader.Read();
                    sendCusCommID = Convert.ToInt32(reader["CusCommID"]);
                    sendCusID = reader["CusID"].ToString();
                }

                cmdEmail.CommandText = "SELECT CusCommID, CusID    FROM LJFIEB_CusComm    WHERE CusCode = 'RUNNIAN'";
                cmdEmail.Parameters.Clear();
                using (var reader = cmdEmail.ExecuteReader())
                {
                    reader.Read();
                    receiveCusCommID = Convert.ToInt32(reader["CusCommID"]);
                    receiveCusID = reader["CusID"].ToString();
                }

                cmdEmail.CommandText = @"
INSERT INTO LJFIEB_packet
	(SerialNum,
	TableName,
	SendCusCommID,
	SendSCcode,
	AddUser,

	AddTime,
	ReceiveCusCommID,
	ReceiveSCcode,
	Status,
	BillType,

	Summary,
	Postscript,
	Remark,
	RelatedCode,
	RelatedSerialNum,

	SendTime,
	cusid,
	SendCusID,
	ReceiveCusID) 

VALUES (@SerialNum,
	'LJFIEB_apicmd',
	@sendCusCommID,
	'',
	'超级用户',

	getdate(),
	@receiveCusCommID,
	'',
	0,
	24,

	'',
	'',
	'',
	'',
	@SerialNum,

	getdate(),
	0,
	@sendCusID,
	@receiveCusID)
";
                cmdEmail.Parameters.Clear();
                cmdEmail.Parameters.AddWithValue("@SerialNum", apicmd.serialNum);
                cmdEmail.Parameters.AddWithValue("@sendCusCommID", sendCusCommID);
                cmdEmail.Parameters.AddWithValue("@receiveCusCommID", receiveCusCommID);
                cmdEmail.Parameters.AddWithValue("@sendCusID", sendCusID);
                cmdEmail.Parameters.AddWithValue("@receiveCusID", receiveCusID);
                cmdEmail.ExecuteNonQuery();
            }
        }

        // 【按钮】打开XML文件
        private void btnOpen_Click(object sender, EventArgs e)
        {
            openFileDialog1.ShowDialog();
            if (!openFileDialog1.FileName.Equals(string.Empty))
            {
                txtFilePath.Text = openFileDialog1.FileName;
            }
        }

        // 【按钮】备份分销的 u_sptmtrlname表
        private void btnBackup_Click(object sender, EventArgs e)
        {
            if (infos == null || infos.Count == 0)
            {
                MessageBox.Show("没有账套信息");
                return;
            }

            DialogResult dr = MessageBox.Show("各分销账套将会生成数据表u_sptmtrlname_backup以作备份，若账套内已经存在数据表u_sptmtrlname_backup，并且是有效的数据，请取消操作！\n开始备份？", "", MessageBoxButtons.YesNoCancel);
            if (dr != DialogResult.Yes)
            {
                MessageBox.Show("操作取消");
                return;
            }

            Trace.Write("----------备份u_sptmtrlname开始----------");

            foreach (var info in infos)
            {
                try
                {
                    BackUp_u_sptmtrlname(info);
                }
                catch (Exception ex)
                {
                    txtMsg.Text += string.Format("失败！！:账套[{0}]备份u_sptmtrlname失败:{1}{2}{2}", info.accountName, ex.Message, System.Environment.NewLine);
                    Trace.Write(string.Format("账套[{0}]备份u_sptmtrlname失败:{1}", info.accountName, ex.ToString()));
                    continue;
                }
                txtMsg.Text += string.Format("PASS:账套[{0}]备份u_sptmtrlname成功{1}{1}", info.accountName, System.Environment.NewLine);
            }

            txtMsg.Text += "执行完成(备份)\n\n";
            MessageBox.Show("备份完成！请检查日志");
        }

        // 备份u_sptmtrlname表
        private void BackUp_u_sptmtrlname(CompanyInfo info)
        {
            using (var conn = new SqlConnection(info.mainDB))
            using (var cmd = conn.CreateCommand())
            {
                conn.Open();

                cmd.CommandText = "if exists (select top 1 '' from sysobjects where name = 'u_sptmtrlname_backup')    drop table u_sptmtrlname_backup";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "select *    into u_sptmtrlname_backup    from u_sptmtrlname";
                cmd.ExecuteNonQuery();
            }
        }

        // 【按钮】测试参照表所在数据库的连接
        private void btnTextRefDB_Click(object sender, EventArgs e)
        {
            if (CheckRefDB())
            {
                MessageBox.Show("测试通过");
            }
            else
            {
                MessageBox.Show("测试不通过！！");
            }
        }

        // 测试参照表连接
        private bool CheckRefDB()
        {
            string connStr = GetDBConnStr(txtRefDBName.Text, txtUser.Text, txtPwd.Text);
            try
            {
                using (var conn = new SqlConnection(connStr))
                using (var cmd = conn.CreateCommand())
                {
                    conn.Open();

                    cmd.CommandText = "select top 1 '' from " + txtTableName.Text;
                    cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                refDBConnStr = string.Empty;
                refTableName = string.Empty;
                Trace.Write("参照表数据库连接失败:" + ex.ToString());
                return false;
            }

            refDBConnStr = connStr;
            refTableName = txtTableName.Text;
            return true;
        }

        // 【按钮】执行参照物料编码更新
        private void btnUpdateMtrlCode_Click(object sender, EventArgs e)
        {
            if (!CheckRefDB())
            {
                MessageBox.Show("参照表数据库连接有误，请确认连接参数");
                return;
            }

            if (infos == null || infos.Count == 0)
            {
                MessageBox.Show("没有账套信息");
                return;
            }

            string refDBName = GetDBName(refDBConnStr);

            using (var conn = new SqlConnection(refDBConnStr))
            using (var cmd = conn.CreateCommand())
            {
                foreach (var info in infos)
                {
                    string cmdText = string.Format(@"
UPDATE {0}.dbo.u_sptmtrlname 
SET
    sptmtrlname = {1}.dbo.{2}.mtrlname, 
    sptmtrlcode = {1}.dbo.{2}.mtrlcode, 
    sptmtrlmode = {1}.dbo.{2}.mtrlmode, 
    sccode = 
        case when {1}.dbo.{2}.handtype like '%艾驰%' then '02'
             when {1}.dbo.{2}.handtype like '%槟城%' then '03'
             when {1}.dbo.{2}.handtype like '%吉森%' then '02'
             when {1}.dbo.{2}.handtype like '%沙发%' then '06'
             when {1}.dbo.{2}.handtype like '%锐奇%' then '03'
             when {1}.dbo.{2}.handtype like '%新宠%' then '05'
             when {1}.dbo.{2}.handtype like '%优米诺%' then '05'
        end, 
    scname = 
        case when {1}.dbo.{2}.handtype like '%艾驰%' then '艾驰'
             when {1}.dbo.{2}.handtype like '%槟城%' then '槟城印象 '
             when {1}.dbo.{2}.handtype like '%吉森%' then '吉森'
             when {1}.dbo.{2}.handtype like '%沙发%' then '软体'
             when {1}.dbo.{2}.handtype like '%锐奇%' then '锐奇'
             when {1}.dbo.{2}.handtype like '%新宠%' then '艾驰(新)'
             when {1}.dbo.{2}.handtype like '%优米诺%' then '优米诺'
        end 
FROM {1}.dbo.{2}
	INNER JOIN {0}.dbo.u_sptmtrlname ON {1}.dbo.{2}.oldcode = {0}.dbo.u_sptmtrlname.sptmtrlcode
", info.mainDBName, refDBName, refTableName); // 0:分销主数据库名  1:参照数据库名  2:参照数据表

                    cmd.CommandText = cmdText;
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        
                    }
                }
            }

            MessageBox.Show("更新参照物料编码完成！请检查日志");
        }
    }
}

//艾驰==艾驰  02
//槟城==槟城印象  03
//吉森==吉森  02
//软体==沙发  06
//锐奇==锐奇  03
//新宠==艾驰(新)  05
//优米诺==优米诺  05